package com.chaosj.tools

import com.alibaba.fastjson.JSONObject
import com.google.common.base.MoreObjects
import com.google.common.base.StandardSystemProperty
import com.google.common.base.Strings
import com.google.common.collect.ArrayTable
import com.google.common.collect.HashBasedTable
import com.google.common.collect.Lists
import com.google.common.collect.Maps
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import java.nio.file.Files
import java.text.DecimalFormat
import java.text.SimpleDateFormat
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.function.Function

/**
 * @Description:
 * @author: caoyangjie
 * @date: 2019年11月2019/11/29日 16:45
 * @Copyright: © 赢时胜
 */
class ExcelUtils {
    static String excelBasePath = "E:\\tmp\\excel"
    static void main(args){
        String sheetName = "微服务拆分"
        List<String> titleName = Arrays.asList("层","聚合","领域对象名称","包名","类名","文件说明")
        List<String> fieldName = Arrays.asList("layer","aggregation","domainName","packageName","className","remark")
        Map<String, List<String>> layers = Maps.newHashMap()
        layers.put("应用层",application())
        layers.put("领域层",domains())
        layers.put("基础层",infrastructure())
        List<String> domains = builderDomains()
        List<Data> list = builderDataLists(layers,domains)

//        ExcelUtils.getInstance().export(sheetName,list,titleName,fieldName,null)

        sheetName = "配置项拆分"
        titleName = Arrays.asList("配置项文件名","命名空间名","原有配置项名","配置项名","配置项值","是否支持实时刷新","说明")
        fieldName = Arrays.asList("fileName","namespace","fieldName","attrName","attrValue","refresh","remark")
        List<PropertiesEntity> entities = Lists.newArrayList()
        int ignoreLineCount = 0
        Files.readAllLines(new File("E:\\tmp\\配置项列表.md").toPath()).forEach({line->
            if( ignoreLineCount==2 ){
                entities.add(new PropertiesEntity(line))
            }else{
                ignoreLineCount++
            }
        })

        ExcelUtils.getInstance().export(sheetName,entities,titleName,fieldName,null)
    }

    private static List<String> application(){
        return Arrays.asList(
                "com.yss.fomp.systemmanager.application.controller.vo.%sVo",
                "com.yss.fomp.systemmanager.application.controller.%sController",
                "com.yss.fomp.systemmanager.application.service.I%sService",
                "com.yss.fomp.systemmanager.application.service.impl.%sServiceImpl"
        )
    }

    private static List<String> domains(){
        return Arrays.asList("com.yss.fomp.systemmanager.domain.%s.entity.%sEntity")
    }

    private static List<String> infrastructure(){
        return Arrays.asList("com.yss.fomp.systemmanager.infrastructure.repository.I%sDao")
    }

    private static List<Data> builderDataLists(Map<String,List<String>> layers,List<String> domains){
        List<Data> result = Lists.newArrayList()
        layers.keySet().sort(false).stream().forEach({layer->
            domains.stream().map({domain->domain.split("#")}).forEach({val->
                layers.get(layer).stream().forEach({packageName->
                    String packName = packageName.substring(0,packageName.lastIndexOf("."))
                    if( packName.contains("%s") ){
                         packName = Strings.lenientFormat(packName,val[2])
                    }
                    String className = Strings.lenientFormat(packageName.substring(packageName.lastIndexOf(".")+1),val[2])
                    Data data = new Data(layer,val[0],Strings.lenientFormat("%s(           %s          )",val[1],val[2]),packName,className,"")
                    result.add(data)
                })
            })
        })
        result
    }

    private static List<String> builderDomains() {
        List<String> domains = Arrays.asList(
                "传真#传真策略#FaxPolicy",
                "传真#传真发送#FaxSender",
                "传真#传真服务#FaxServer",
                "传真#传真状态#FaxStatus",
                "传真#关联三方用户#FaxThridUser",
                "邮件#邮件账户#MailAccount",
                "邮件#邮件联系人#MailContact",
                "邮件#邮件联系人分组#MailContactGroup",
                "邮件#邮件策略#MailPolicy",
                "邮件#邮件归档#MailSection",
                "邮件#邮件服务#MailServer",
                "邮件#邮件发送#MailSender",
                "短信#短信联系人#SmsContact",
                "短信#短信策略#SmsPolicy",
                "短信#短信发送#SmsSender",
                "短信#短信服务#SmsServer",
                "配置#通用配置#CommonConfig",
                "配置#系统枚举服务#EnumsConfig",
                "字典#自定义字典#DictCustom",
                "字典#关键字字典#DictSysWord",
                "字典#业务词汇字典#DictVocabulary",
                "字典#fast词汇字典#DictFastVoc",
                "OA管理#部门#OfficeDepartment",
                "OA管理#分部#OfficeCompany",
                "OA管理#员工#OfficeUser",
                "系统用户#用户管理#User",
                "系统用户#组织机构#CorpOrg",
                "系统用户#机器码#UserMac",
                "系统用户#用户岗位#UserPost",
                "系统用户#密码历史#UserPwdHist",
                "自定义功能#自定义菜单#CustomFun",
                "自定义功能#自定义数据分类#CustomData",
                "自定义功能#自定义菜单方案#CustomFunPlan",
                "导入导出#导入服务#CommonReport",
//                "导入导出#导出服务#",
//                "导入导出#导出列方案服务#",
                "数据处理#数据审核#DataAudit",
                "数据处理#数据拷贝#DataCopy",
                "数据处理#数据拷贝自定义#DataCopyCustom",
                "数据处理#数据检查#DataCheck",
                "文件功能#外围文件数据#FileEnclosure",
                "文件功能#文件全路径服务#FilePath",
                "文件功能#文件删除服务#FileDelete",
                "文件功能#文件清理服务#FileClear",
                "文件功能#授权文件服务#FileLicense",
                "日志功能#操作日志服务#OperateLog",
                "日志功能#业务日志服务#BusinessLog",
                "日志功能#日志下载服务#LogDown",
                "日志功能#日志配置服务#LogConfig",
                "消息功能#消息管理服务#MsgManager",
                "消息功能#消息发送服务#MsgSender",
                "消息功能#消息订阅服务#MsgSubscribe",
                "消息功能#消息类型服务#MsgType",
                "参数校验功能#参数校验服务#ValidParams",
                "SQL管理功能#SQL管理服务#SqlManager",
                "资源功能#数据库资源服务#DbResource",
                "资源功能#FTP资源服务#FtpResource",
                "资源功能#用户自定义功能服务#UseCustomResource",
                "资源功能#资源管理服务#ManagerResource",
                "资源功能#在线用户服务#UserOnLine",
                "资源功能#服务列表查询服务#ServiceFinder",
                "资源功能#webService管理服务#WebSrvManager",
                "资源功能#版本管理服务#VersionManager",
                "安全功能#系统配置安全#SecurityCfg",
                "安全功能#系统数据安全#SecurityData",
                "安全功能#功能菜单服务#SecurityFun",
                "签章功能#签章文件数据服务#SealFileData",
                "签章功能#签章文件服务#SealFile",
                "签章功能#签章业务服务#ElecSealRel",
                "签章功能#签章权限服务#ElecSealRight",
                "签章功能#电子公章服务#ElectronicSeal",
                "工作空间功能#公告表服务#WorkSpaceNotice",
                "工作空间功能#公共面板服务#VatPlanSecPanel",
                "工作空间功能#大盘模拟功能服务#WorkSpacePlan",
                "工作空间功能#工作空间服务#WorkSpace",
                "工作空间功能#面板参数服务#WorkSpaceConfigParam"
        )
        return domains
    }


    private static ThreadLocal<ExcelUtils> currThreadLocal = new ThreadLocal<ExcelUtils>()
    /**
     * 2003- 版本的excel
     */
    private final static String excel2003L = ".xls"
    /**
     * 2007+ 版本的excel
     */
    private final static String excel2007U = ".xlsx"

    /**
     * 实例化对象
     *
     * @return ListToExcelUtils
     */
    static ExcelUtils getInstance() {
        if (currThreadLocal.get() == null) {
            ExcelUtils excelUtils = new ExcelUtils()
            currThreadLocal.set(excelUtils)
            return excelUtils
        } else {
            return currThreadLocal.get()
        }
    }

    private ExcelUtils() {
    }

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     *
     * @return list
     */
    Map<String, Object> getExcelData(InputStream inStream, String fileName, List<String> models, List<String[]> columns) {
        Map result = new LinkedHashMap()
        try {
            Map<String, Object> data = new LinkedHashMap<String, Object>()
            //创建Excel工作薄
            Workbook work = this.getWorkbook(inStream, fileName)
            if (null == work) {
                throw new Exception("创建Excel工作薄为空！")
            }
            //读取数据
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                List<List<Object>> pList = new ArrayList<List<Object>>()
                Sheet sheet = work.getSheetAt(i)
                int rows = sheet.getPhysicalNumberOfRows()
                //遍历Excel中所有的sheet
                for (int r = 1; r <= rows; r++) {
                    Row row = sheet.getRow(r)
                    if (row != null) {
                        int cells = row.getLastCellNum()
                        List<Object> cList = new ArrayList<Object>()
                        for (int c = 0; c < cells; c++) {
                            Cell cell = row.getCell(c)
                            if (cell != null) {
                                cList.add(getCellValue(cell))
                            }
                        }
                        pList.add(cList)
                    }
                }
                data.put(sheet.getSheetName(), pList)
            }
            work.close()
            //封装数据
            int index = 0
            for (String key : data.keySet()) {
                List<List<Object>> list = (List<List<Object>>) data.get(key)
                List<String> modelList = new ArrayList<String>()
                for (List<Object> lo : list) {
                    Map columnContent = new LinkedHashMap()
                    int j = 0
                    for (String column : columns.get(index)) {
                        columnContent.put(column, lo.get(j))
                        j++
                    }
                    modelList.add(JsonTools.toJson(columnContent))
                }
                result.put(models.get(index), modelList)
                index++
            }
        } catch (Exception e) {
            logger.error("导入excel出错 {}", e)
        } finally {
            // 操作完成后释放当前对象
            currThreadLocal.remove()
        }
        return result
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb
        String fileType = fileName.substring(fileName.lastIndexOf("."))
        if (excel2003L == fileType) {
            /**
             * 2003-
             */
            wb = new HSSFWorkbook(inStr)
        } else if (excel2007U  == fileType) {
            /**
             * 2007+
             */
            wb = new XSSFWorkbook(inStr)
        } else {
            throw new Exception("解析的文件格式有误！")
        }
        return wb
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    private Object getCellValue(Cell cell) {
        Object value = null
        /**
         * 格式化number String字符
         */

        DecimalFormat df = new DecimalFormat("0")
        /**
         * 日期格式化
         */
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd")
        /**
         * 格式化数字
         */
        DecimalFormat df2 = new DecimalFormat("0.00")

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString()
                break
            case Cell.CELL_TYPE_NUMERIC:
                if ("General" == cell.getCellStyle().getDataFormatString()) {
                    value = df.format(cell.getNumericCellValue())
                } else if ("m/d/yy" == cell.getCellStyle().getDataFormatString()) {
                    value = sdf.format(cell.getDateCellValue())
                } else {
                    value = df2.format(cell.getNumericCellValue())
                }
                break
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue()
                break
            case Cell.CELL_TYPE_BLANK:
                value = ""
                break
            default:
                break
        }
        return value
    }

    /**
     * 导出为Excel文件
     *
     * @param sheetName  sheet页的名称
     * @param data       导出的数据
     * @param titles     sheet页表头
     * @param fieldNames 要导出数据的列
     * @param basePath   导出文件的基础目录
     * @return
     */
    File export(String sheetName, List data, List<String> titles, List<String> fieldNames,String basePath) {
        basePath = MoreObjects.firstNonNull(basePath,excelBasePath)
        println(String.format("开始导出excel:%s,size:%s,titles.length:%s,fieldnames.length:%s", sheetName, data.size(), titles.size(), fieldNames.size()))
        if (data.size() > 60000) {
            println("不允许超过excel的60000行")
            /**
             * 提前发现问题，减少后面的性能损失。需要在客户端调用之前还要做限定会更完善，
             */
            throw new IndexOutOfBoundsException("不允许超过excel的60000行")
        }
        HSSFWorkbook wb = new HSSFWorkbook()

        HSSFSheet sheet

        // 对每个表生成一个新的sheet,并以表名命名
        if (sheetName == null) {
            sheetName = "sheet1"
        }

        sheet = wb.createSheet(sheetName)

        // 设置表头的说明
        HSSFRow topRow = sheet.createRow(0)

        for (int i = 0; i < titles.size(); i++) {
            setCellGBKValue(topRow.createCell((short) i), titles[i])
        }

        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1)
            JSONObject obj =  (JSONObject) JSONObject.toJSON(data.get(i))

            for (int j = 0; j < fieldNames.size(); j++) {
                setCellGBKValue(row.createCell((short) j), obj.get(fieldNames[j]) == null ? "" : obj.get(fieldNames[j]).toString())
            }
        }

        println(Strings.lenientFormat("导出文件临时目录：%s",basePath))
        String filename = Strings.lenientFormat("%s_%s.%s",sheetName,LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS")),"xls")
        File file = new File(basePath + StandardSystemProperty.FILE_SEPARATOR.value() + filename)
        file.mkdirs()
        if( file.exists() ){
            Files.delete(file.toPath())
        }
        Files.createFile(file.toPath())
        try {
            OutputStream os = new FileOutputStream(file)
            wb.write(os)
            os.flush()
            os.close()
        } catch (Exception e) {
            println(e.getMessage())

            return null
        }
        println(String.format("导出excel结束:%s,size:%s,titles.length:%s,fieldnames.length:%s", sheetName, data.size(), titles.size(), fieldNames.size()))
        // 操作完成后释放当前对象
        currThreadLocal.remove()
        return file
    }

    @SuppressWarnings("deprecation")
    private static void setCellGBKValue(HSSFCell cell, String value) {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING)
        cell.setCellValue(value)
    }
}
